/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package classes;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Alvaro
 */
public class graph {

    int x;
    int y;

    public int getX() {
        return x;
    }

    public void setX(int x) {
        this.x = x;
    }

    public int getY() {
        return y;
    }

    public void setY(int y) {
        this.y = y;
    }

    public graph(int x, int y) {
        this.x = x;
        this.y = y;
    }

    public graph() {
    }

    public String[] getPointsX(Connection conn) throws SQLException {
        ResultSet rset = null;
        try {
            PreparedStatement stmt = null;

            if (null == stmt) {
                stmt = conn.prepareStatement("SELECT * FROM `F1shopOnline`.`product`");
            }
            rset = stmt.executeQuery();
            String[] gx = new String[100];
            String x;
            int y;
            int i = 0;
            while (rset.next()) {
                x = rset.getString("name");
                gx[i] = x;
                i++;
            }
            return gx;
        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return null;
        } catch (Exception e) {
            Logger.getLogger("Graph").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return null;
        }finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException e) {
                    Logger.getLogger("GetPX").log(Level.SEVERE, "ERROR AL CERRAR RSET: " + e.getLocalizedMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("GetPX").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }

    }

    public int[] getPointsBestsellersY(Connection conn) throws SQLException {
        ResultSet rset = null;
        try {
            PreparedStatement stmt = null;
            if (null == stmt) {
                stmt = conn.prepareStatement("SELECT * FROM `F1shopOnline`.`product`");
            }
            rset = stmt.executeQuery();
            int[] gy = new int[100];
            int x;
            int y;
            int i = 0;
            while (rset.next()) {
                y = rset.getInt("purchasedtimes");
                gy[i] = y;
                i++;
            }

            return gy;
        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return null;
        } catch (Exception e) {
            Logger.getLogger("Graph").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return null;
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException e) {
                    Logger.getLogger("GetPY").log(Level.SEVERE, "ERROR AL CERRAR RSET: " + e.getLocalizedMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("GetPY").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }

    }

    public float[] getPointsStockY(Connection conn) throws SQLException {
        ResultSet rset = null;
        try {
            PreparedStatement stmt = null;
            if (null == stmt) {
                stmt = conn.prepareStatement("SELECT * FROM `F1shopOnline`.`product`");
            }
            rset = stmt.executeQuery();
            float[] gy = new float[100];
            float y;
            int i = 0;
            while (rset.next()) {
                y = rset.getFloat("stock");
                gy[i] = y;
                i++;
            }
            return gy;
        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return null;
        } catch (Exception e) {
            Logger.getLogger("Graph").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return null;
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException e) {
                    Logger.getLogger("GetPYStock").log(Level.SEVERE, "ERROR AL CERRAR RSET: " + e.getLocalizedMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("GetPYStock").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }

    }

    public int getPointsDayY(Connection conn, String day, String number, String month, String year) throws SQLException {
        ResultSet rset = null;
        try {
            PreparedStatement stmt = null;
            if (null == stmt) {
                stmt = conn.prepareStatement("SELECT COUNT(*) FROM `F1shopOnline`.`order` WHERE paymentdate LIKE '" + day + "%" + month + "%" + number + "%" + year + "%'");
            }
            rset = stmt.executeQuery();
            int y;
            int i = 0;
            rset.next();
            System.out.println("Number of rows :" + rset.getInt(1));

            return rset.getInt(1);
        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return 0;
        } catch (Exception e) {
            Logger.getLogger("Graph").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return 0;
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException e) {
                    Logger.getLogger("GETPYDay").log(Level.SEVERE, "ERROR AL CERRAR RSET: " + e.getLocalizedMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("GETPYDay").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }
    }

    //Best Customers
    public String[] getPointsCustomersX(Connection conn) throws SQLException {
        ResultSet rset = null;
        try {
            PreparedStatement stmt = null;

            if (null == stmt) {
                stmt = conn.prepareStatement("SELECT * FROM `F1shopOnline`.`user`");
            }
            rset = stmt.executeQuery();
            String[] gx = new String[100];

            String x;
            int y;
            int i = 0;
            while (rset.next()) {
                x = rset.getString("email");
                gx[i] = x;
                i++;
            }
            return gx;
        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return null;
        } catch (Exception e) {
            Logger.getLogger("Graph").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return null;
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException e) {
                    Logger.getLogger("GetPXCustomer").log(Level.SEVERE, "ERROR AL CERRAR RSET: " + e.getLocalizedMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("GetPXCustomer").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }

    }

    public int[] getPointsCustomersY(Connection conn) throws SQLException {
        ResultSet rset = null;
        try {
            PreparedStatement stmt = null;
            if (null == stmt) {
                stmt = conn.prepareStatement("SELECT * FROM `F1shopOnline`.`user`");
            }
            rset = stmt.executeQuery();
            int[] gy = new int[100];
            int x;
            int y;
            int i = 0;
            while (rset.next()) {
                y = rset.getInt("purchases");
                gy[i] = y;
                i++;
            }
            return gy;
        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return null;
        } catch (Exception e) {
            Logger.getLogger("Graph").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return null;
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException e) {
                    Logger.getLogger("GetPYCustomer").log(Level.SEVERE, "ERROR AL CERRAR RSET: " + e.getLocalizedMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("GetPYCustomer").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }

    }

    public int getNumberUsers(Connection conn) throws SQLException {
        ResultSet rset = null;
        try {
            PreparedStatement stmt = null;

            if (null == stmt) {
                stmt = conn.prepareStatement("SELECT * FROM `F1shopOnline`.`user`");
            }
            rset = stmt.executeQuery();
            int y;
            int i = 0;
            rset.next();
            System.out.println("Number of rows in user:" + rset.getInt(1));


            return rset.getInt(1);
        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return 0;
        } catch (Exception e) {
            Logger.getLogger("Graph").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return 0;
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException e) {
                    Logger.getLogger("NummberUsers").log(Level.SEVERE, "ERROR AL CERRAR RSET: " + e.getLocalizedMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("NummberUsers").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }


    }

    public int getNumberProducts(Connection conn) throws SQLException {
        ResultSet rset = null;
        try {
            PreparedStatement stmt = null;

            if (null == stmt) {
                stmt = conn.prepareStatement("SELECT COUNT(*) FROM `F1shopOnline`.`product`");
            }
            rset = stmt.executeQuery();

            int y;
            int i = 0;
            rset.next();
            System.out.println("Number of rows in product:" + rset.getInt(1));

            return rset.getInt(1);

        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return 0;
        } catch (Exception e) {
            Logger.getLogger("Graph").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return 0;
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException e) {
                    Logger.getLogger("NumebrProd").log(Level.SEVERE, "ERROR AL CERRAR RSET: " + e.getLocalizedMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("NumebrProd").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }
    }
}
